Applying NOT NULL and DEFAULT Constraints in MySQL

Course: MySQL Training

Topic: Applying NOT NULL and DEFAULT Constraints

Course Link: MySQL Training Course at JustAcademy

Demo Registration: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Constraints in MySQL

In MySQL, constraints are rules applied to database columns to maintain data accuracy, consistency, and reliability. They help control what type of information can be stored in a table and prevent invalid or incomplete data from entering the database.

For example, a student record may require a student name, while a country field may automatically receive a default value if the user does not provide one. MySQL provides constraints such as NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK to handle these requirements.

This topic focuses on two important constraints:

• NOT NULL: Prevents a column from storing SQL NULL values.
• DEFAULT: Supplies a predefined value when an INSERT statement does not provide a value for the column, subject to MySQL's default-value rules.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand the purpose of database constraints.
• Explain the difference between NULL, zero, an empty string, and a default value.
• Create tables using NOT NULL and DEFAULT constraints.
• Insert records while using default column values.
• Modify existing table columns using ALTER TABLE.
• Understand how omitted columns, explicit NULL values, and DEFAULT keywords behave.
• Identify common errors related to NOT NULL and DEFAULT.
• Apply these constraints to practical database scenarios.

3. Understanding NULL in MySQL

NULL represents a missing, unknown, or unavailable value. It is different from zero, an empty string, or a space.

These values are not interchangeable. For example, a quantity of zero means that no units are available, while NULL may mean that the quantity has not yet been recorded.

3.1 Checking for NULL

Use IS NULL or IS NOT NULL to test for NULL values. Do not use the equals operator to test for NULL.

SELECT *
FROM students
WHERE middle_name IS NULL;

SELECT *
FROM students
WHERE middle_name IS NOT NULL;

4. Understanding the NOT NULL Constraint

The NOT NULL constraint specifies that a column must contain a non-NULL value. It is useful for fields that are essential to a record, such as a product name, employee name, or order date.

4.1 Why Use NOT NULL?

• Prevents required information from being stored as NULL.
• Improves data consistency.
• Ensures important fields contain values.
• Helps enforce business rules at the database level.
• Reduces the need to handle missing values in application code.

4.2 Basic Syntax

CREATE TABLE table_name (
    column_name datatype NOT NULL
);

In this syntax, the column is defined with a data type and the NOT NULL constraint.

5. Creating a Table with NOT NULL

Consider a student database. Each student must have a student ID and a name. The name column should not accept NULL values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    age INT
);

In this table:

• student_id uniquely identifies each student.
• student_name is required and cannot be NULL.
• email can contain NULL because NOT NULL was not specified.
• age can contain NULL because it is optional in this example.

5.1 Inserting a Valid Record

INSERT INTO students
(student_id, student_name, email, age)
VALUES
(1, 'Aarav Sharma', '[email protected]', 21);

This statement succeeds if the table is empty or the supplied student ID does not conflict with an existing primary key.

5.2 Attempting to Insert NULL

INSERT INTO students
(student_id, student_name, email, age)
VALUES
(2, NULL, '[email protected]', 20);

This statement fails because student_name is defined as NOT NULL. In a typical MySQL configuration, the server reports an error indicating that the column cannot be NULL.

5.3 Omitting a NOT NULL Column

INSERT INTO students (student_id, email, age)
VALUES (3, '[email protected]', 22);

The statement does not provide a value for student_name. Since the column is NOT NULL and has no declared default, the statement normally fails in strict SQL mode. In non-strict configurations, MySQL may apply an implicit type-specific value and issue a warning. Applications should not rely on that behavior.

6. Understanding the DEFAULT Constraint

The DEFAULT constraint defines a value that MySQL can use when an INSERT statement omits a column or explicitly requests its default using the DEFAULT keyword. It is helpful when a field commonly starts with the same value.

Examples include:

• A user's account status defaults to 'Active'.
• A product's stock quantity defaults to 0.
• A user's country defaults to a commonly selected country when appropriate for the application.
• A record's creation timestamp can be initialized automatically using a timestamp default expression.

6.1 Basic Syntax

CREATE TABLE table_name (
    column_name datatype DEFAULT default_value
);

The default value must be compatible with the column's data type and the rules supported by the MySQL version in use.

7. Creating a Table with DEFAULT

Suppose an online store stores product details. New products should start with zero stock and an active status unless another value is supplied.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'Active'
);

Explanation:

• product_id identifies each product.
• product_name is mandatory.
• stock_quantity defaults to 0 when omitted.
• status defaults to 'Active' when omitted.

7.1 Inserting a Record Without Defaulted Columns

INSERT INTO products (product_id, product_name)
VALUES (101, 'Wireless Mouse');

Because stock_quantity and status are omitted, MySQL uses their declared defaults.

The resulting record is conceptually represented as follows:

7.2 Inserting a Custom Value

INSERT INTO products
(product_id, product_name, stock_quantity, status)
VALUES
(102, 'Keyboard', 25, 'Inactive');

Explicitly supplied values take precedence over the defaults. The stock quantity becomes 25 and the status becomes 'Inactive'.

7.3 Using the DEFAULT Keyword

You can explicitly request the default value by using DEFAULT in the INSERT statement.

INSERT INTO products
(product_id, product_name, stock_quantity, status)
VALUES
(103, 'USB Hub', DEFAULT, DEFAULT);

This inserts the product with the declared default stock quantity and status.

8. Difference Between NOT NULL and DEFAULT

Although these constraints can be used together, they perform different jobs.

8.1 Using Both Constraints Together

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL DEFAULT 'General',
    salary DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

In this example, employee name is mandatory. The department and salary columns are also NOT NULL, but they have defaults so that an INSERT statement can omit them and still receive valid non-NULL values.

9. How NOT NULL and DEFAULT Work During INSERT

Understanding the difference between omitting a column, supplying NULL, and requesting DEFAULT is essential when inserting records.

Important: A DEFAULT value is not automatically substituted whenever someone supplies NULL. If a nullable column receives explicit NULL, MySQL normally stores NULL. If a NOT NULL column receives explicit NULL, the operation normally fails. Exact behavior can depend on SQL mode, data type, and statement context.

10. Applying NOT NULL to an Existing Column

You can modify a column in an existing table using ALTER TABLE. Before making a column NOT NULL, inspect existing data because rows containing NULL may prevent the change from succeeding.

10.1 Inspect Existing Records

SELECT *
FROM students
WHERE student_name IS NULL;

This query identifies rows that contain NULL in the student name column.

10.2 Modify the Column

If the column is a VARCHAR(100), you can change its definition as follows:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(100) NOT NULL;

Use the correct existing data type and preserve other required attributes, such as a DEFAULT value, when modifying a column. MySQL's MODIFY COLUMN syntax specifies the new column definition, so omitting an existing attribute may change that definition.

10.3 Handle Existing NULL Values Before the Change

If existing rows contain NULL, decide how to correct the records before applying NOT NULL. The replacement should be meaningful and consistent with the business rules.

UPDATE students
SET student_name = 'Unknown'
WHERE student_name IS NULL;

Only use a placeholder such as 'Unknown' if it is acceptable for your application. If accurate names are required, recover the correct information instead. After resolving the data, apply the ALTER TABLE statement.

11. Applying or Changing a DEFAULT Value

Use ALTER TABLE with ALTER COLUMN ... SET DEFAULT to define or change a column's default value in MySQL.

11.1 Add a Default Value

ALTER TABLE products
ALTER COLUMN status SET DEFAULT 'Active';

11.2 Change the Default Value

ALTER TABLE products
ALTER COLUMN stock_quantity SET DEFAULT 10;

After the change, new inserts that omit stock_quantity will use 10 rather than 0. Existing records are not automatically updated just because the default changes.

11.3 Remove a Default Value

ALTER TABLE products
ALTER COLUMN status DROP DEFAULT;

Removing a default does not automatically make the column NOT NULL or nullable. The column's nullability and default are separate properties. If the column has no explicit default, MySQL applies its normal rules for the data type, SQL mode, and INSERT statement.

12. Using DEFAULT with Different Data Types

12.1 VARCHAR and CHAR

CREATE TABLE user_accounts (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    account_status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

String defaults should be enclosed in single quotes. Here, account status defaults to 'Active'.

12.2 Integer Columns

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0
);

The default value 0 is appropriate when a new item initially has no recorded stock. Confirm that this meaning matches the business requirements.

12.3 DECIMAL Columns

CREATE TABLE services (
    service_id INT PRIMARY KEY,
    service_name VARCHAR(100) NOT NULL,
    base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

DECIMAL is suitable for values such as prices that require fixed decimal precision. A zero price should only be used as a default if it is a valid business value.

12.4 Date and Time Columns

MySQL supports current date and time defaults for applicable temporal column definitions. For example, a creation timestamp can be initialized automatically.

CREATE TABLE audit_records (
    record_id INT PRIMARY KEY,
    description VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

When a record is inserted without created_at, the current timestamp is used according to MySQL's timestamp and default-expression rules.

13. Complete Practical Example: Employee Management

In this example, an organization needs to store employee information. Each employee must have a name. Department, employment status, and salary have suitable starting values.

13.1 Create the Table

CREATE TABLE employee_records (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL DEFAULT 'General',
    employment_status VARCHAR(20) NOT NULL DEFAULT 'Active',
    salary DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    email VARCHAR(150) DEFAULT NULL
);

13.2 Insert a Record Using Defaults

INSERT INTO employee_records (employee_name)
VALUES ('Neha Patel');

MySQL generates the employee ID. The department becomes 'General', employment status becomes 'Active', and salary becomes 0.00. The email is NULL because its default is explicitly declared as NULL.

13.3 Insert a Record with Custom Values

INSERT INTO employee_records
(employee_name, department, employment_status, salary, email)
VALUES
('Rahul Mehta', 'IT', 'Active', 45000.00, '[email protected]');

13.4 Display the Records

SELECT *
FROM employee_records;

13.5 Test NOT NULL Behavior

INSERT INTO employee_records (employee_name)
VALUES (NULL);

This statement normally fails because employee_name is defined as NOT NULL.

13.6 Test the DEFAULT Keyword

INSERT INTO employee_records
(employee_name, department, employment_status, salary)
VALUES
('Amit Shah', DEFAULT, DEFAULT, DEFAULT);

The statement explicitly requests the default values for the three specified columns.

14. Verifying Column Constraints

After creating or modifying a table, inspect its definition to verify that the constraints were applied correctly.

14.1 Use DESCRIBE

DESCRIBE employee_records;

The output includes information such as column name, data type, whether NULL is permitted, key information, and default values.

14.2 Use SHOW CREATE TABLE

SHOW CREATE TABLE employee_records;

This statement displays the table's creation definition, including column definitions and constraints. It is useful for checking the exact schema configured in MySQL.

14.3 Inspect Column Metadata

SELECT
    COLUMN_NAME,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'employee_records';

This query returns the nullability, default value, and data type for each column in the specified table within the current database.

15. Common Errors and Troubleshooting

15.1 Check the SQL Mode

SELECT @@sql_mode;

SQL mode affects how MySQL handles invalid or missing values. Strict mode generally rejects invalid values and missing values for required columns more consistently. Avoid relying on implicit values or warnings as a substitute for a correctly designed schema.

15.2 Confirm the Column Definition

SHOW CREATE TABLE products;

Review the column's data type, NULL setting, and DEFAULT definition. Ensure that the INSERT statement matches the intended table structure.

16. Best Practices for NOT NULL and DEFAULT

• Use NOT NULL for fields that must always contain meaningful values.
• Use DEFAULT only when a standard starting value is appropriate.
• Do not use arbitrary placeholder values merely to avoid NULL.
• Distinguish between unknown information and a meaningful value such as zero.
• Before changing an existing nullable column to NOT NULL, identify and resolve existing NULL records.
• Test table changes in a development environment before applying them to production.
• Use explicit column lists in INSERT statements to make queries easier to understand and maintain.
• Use DESCRIBE or SHOW CREATE TABLE to verify the resulting schema.
• Keep the application's validation rules consistent with the database constraints.
• Document the reason for important defaults so that future developers understand their meaning.

17. Practice Exercises

Exercise 1: Student Table

Create a table named student_details with the following requirements:

• student_id is an integer primary key.
• student_name is VARCHAR(100) and NOT NULL.
• course_name is VARCHAR(100) with a default of 'MySQL'.
• attendance_percentage is DECIMAL(5,2) with a default of 0.00.

Sample solution:

CREATE TABLE student_details (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    course_name VARCHAR(100) DEFAULT 'MySQL',
    attendance_percentage DECIMAL(5,2) DEFAULT 0.00
);

Exercise 2: Insert a Student Using Defaults

INSERT INTO student_details (student_id, student_name)
VALUES (1, 'Priya Singh');

Verify that the course name is 'MySQL' and the attendance percentage is 0.00.

Exercise 3: Insert an Invalid Record

INSERT INTO student_details (student_id, student_name)
VALUES (2, NULL);

Explain why this statement normally fails.

Exercise 4: Modify an Existing Column

Suppose a column called course_name should no longer permit NULL. First inspect existing records, correct any NULL values where appropriate, and then apply the NOT NULL definition while preserving the intended default.

UPDATE student_details
SET course_name = 'MySQL'
WHERE course_name IS NULL;

ALTER TABLE student_details
MODIFY COLUMN course_name VARCHAR(100)
NOT NULL DEFAULT 'MySQL';

Exercise 5: Change a Default

Change the default attendance percentage to 5.00 and insert a new student without specifying the attendance percentage.

ALTER TABLE student_details
ALTER COLUMN attendance_percentage SET DEFAULT 5.00;

INSERT INTO student_details (student_id, student_name)
VALUES (3, 'Karan Joshi');

Check the inserted record and explain why the new default is applied to the omitted column.

18. Mini Project: Product Inventory Database

Project objective: Create a product inventory table that requires a product name while providing appropriate default values for stock and product status.

Requirements

• Use an auto-incrementing product ID as the primary key.
• Require a product name using NOT NULL.
• Set the initial quantity to zero by default.
• Set the product status to 'Available' by default.
• Allow an optional description.

Solution

CREATE TABLE product_inventory (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(120) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    product_status VARCHAR(20) NOT NULL DEFAULT 'Available',
    description VARCHAR(255) DEFAULT NULL
);

INSERT INTO product_inventory (product_name)
VALUES ('Laptop Stand');

INSERT INTO product_inventory
(product_name, quantity, product_status, description)
VALUES
('USB-C Adapter', 15, 'Available', 'Multiport adapter');

SELECT *
FROM product_inventory;

Project Tasks

1. Insert at least five products.
2. Insert a product without providing quantity or status.
3. Try inserting NULL into the product name and observe the error.
4. Change the default product status to 'In Stock'.
5. Use SHOW CREATE TABLE to inspect the schema.
6. Explain why a default value does not replace an explicitly supplied NULL in a nullable column.

19. Interview Questions and Answers

Q1. What is the NOT NULL constraint?

Answer: NOT NULL prevents a column from storing SQL NULL values. It is used for information that must be present in every valid record.

Q2. What is the DEFAULT constraint?

Answer: DEFAULT defines a value MySQL can use when an INSERT statement omits a column or explicitly requests DEFAULT.

Q3. Can NOT NULL and DEFAULT be used together?

Answer: Yes. A column can be NOT NULL and have a default value. When the column is omitted, MySQL can use that non-NULL default.

Q4. What happens if NULL is inserted into a NOT NULL column?

Answer: MySQL normally rejects the statement with an error. Exact behavior can depend on SQL mode and statement context.

Q5. Does DEFAULT automatically replace an explicitly supplied NULL?

Answer: No. An explicit NULL is not the same as omitting the column or writing DEFAULT. A nullable column normally stores the supplied NULL, while a NOT NULL column normally rejects it.

Q6. How do you add NOT NULL to an existing column?

Answer: Use ALTER TABLE with MODIFY COLUMN and specify the full intended column definition. Check and resolve existing NULL values first.

Q7. How do you change a default value?

Answer: Use ALTER TABLE with ALTER COLUMN ... SET DEFAULT, for example:

ALTER TABLE products
ALTER COLUMN status SET DEFAULT 'Active';

Q8. Does changing a default update existing records?

Answer: No. Changing a default affects future inserts where the default is used. Existing records require a separate UPDATE if they need to change.

Q9. How can you verify a column's default?

Answer: Use DESCRIBE table_name, SHOW CREATE TABLE table_name, or query INFORMATION_SCHEMA.COLUMNS.

Q10. What is the difference between NULL and an empty string?

Answer: NULL represents a missing or unknown value. An empty string is a valid string containing zero characters.

20. Quick Revision Checklist

• NOT NULL prevents SQL NULL values in a column.
• DEFAULT supplies a predefined value when the column is omitted or DEFAULT is requested.
• NOT NULL does not automatically create a default value.
• A column can use NOT NULL and DEFAULT together.
• An explicit NULL is different from omitting a column.
• Use IS NULL and IS NOT NULL to check for NULL values.
• Use ALTER TABLE to modify column definitions and defaults.
• Check existing records before applying NOT NULL to an existing column.
• Use DESCRIBE and SHOW CREATE TABLE to inspect column definitions.
• Test constraint changes before applying them to production databases.

21. Conclusion

The NOT NULL and DEFAULT constraints are fundamental tools for designing reliable MySQL databases. NOT NULL ensures that required columns do not contain SQL NULL, while DEFAULT supplies an appropriate predefined value when a column is omitted or DEFAULT is explicitly requested. Using these constraints together can simplify data entry, enforce business rules, and improve data consistency.

Practice creating tables, inserting records, modifying column definitions, and inspecting schemas to understand how these constraints behave in real database applications.

Continue Learning: Visit the JustAcademy MySQL Training Course, explore the JustAcademy website, or register for a course demo.